DECLARE @DATEUPDATE DATE = (CONVERT(DATETIME,(SELECT CONVERT(NVARCHAR(10),DATE_UPDATE) FROM DATE_EM_REPORT)))

 IF EXISTS(SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN ('U') AND O.NAME = 'T24CRD_OVERDUE_LAST_DATE_RAW')
  DROP TABLE T24CRD_OVERDUE_LAST_DATE_RAW

SELECT *
INTO T24CRD_OVERDUE_LAST_DATE_RAW
FROM SERVER16.[VPB_WHR2].[DBO].[T24CRD] B 
WHERE BUSINESS_DATE = @DATEUPDATE 
		AND EXISTS (SELECT *
            FROM   TBL_CUSTOMERS A 
            WHERE A.CIF = B.CUSTOMER_ID) 
		AND NO_OVD_DAYS BETWEEN 1 AND 90


--LDPD_AMOUNT = OUSTANDING_AMNT + PR_AMT
--IN_AMT = LAI PHAI TRA
--PE_AMT = LAI QUA HAN
--PS_AMT = LAI PHAT QUA HAN


--SELECT * FROM T24CRD_OVERDUE_LAST_DATE_RAW

--DROP TABLE TBL_CM_ALERT_OVERDUE

DELETE FROM TBL_CM_ALERT_OVERDUE

INSERT TBL_CM_ALERT_OVERDUE
SELECT A.BUSINESS_DATE, B.CIF, B.CUS_NAME, A.CONTRACT_NO, A.CURRENCY, A.VALUE_DATE, A.MATURITY_DATE, A.TERM, A.NO_OVD_DAYS, ISNULL(A.LDPD_AMT,0)*C.MID_RATE AS DU_NO_GOC,ISNULL(IN_AMT,0) * C.MID_RATE AS LAI_TRONG_KY, ISNULL(PE_AMT,0) * C.MID_RATE AS LAI_QUA_HAN, ISNULL(PS_AMT,0) * C.MID_RATE AS LAI_PHAT , D.BRANCH_CODE_SME, D.BRANCH_NAME_SME, B.DAO AS DAO, B.DAO_NAME AS DAO_NAME, B.INDUSTRY_NAME, B.INDUSTRY_NAME_EN, D.REGION
--into TBL_CM_ALERT_OVERDUE
FROM T24CRD_OVERDUE_LAST_DATE_RAW A, TBL_CUSTOMER B, [SERVER16].[VPB_WHR2].DBO.FOCURR_SAVE C, TBL_BRANCH D
WHERE A.CUSTOMER_ID = B.CIF AND C.SAVE_DATE = @DATEUPDATE AND A.CURRENCY=C.CODE  AND B.BRANCH_ID = D.BRANCH_ID


--SELECT * FROM TBL_CM_ALERT_OVERDUE

--INSERT TBL_DATE_BUSINESS
--SELECT MAX(BUSINESS_DATE) AS DATE_DATA, 'TBL_CM_ALERT_OVERDUE' FROM TBL_CM_ALERT_OVERDUE
--DELETE FROM TBL_DATE_BUSINESS WHERE ID = 19
UPDATE B
SET B.DATE_DATA = A.BUSINESS_DATE
FROM (SELECT MAX(BUSINESS_DATE) AS BUSINESS_DATE FROM TBL_CM_ALERT_OVERDUE) A,  TBL_DATE_BUSINESS B
WHERE B.NAME_TABLE = 'TBL_CM_ALERT_OVERDUE'
